﻿Imports System.Data.SqlClient
Imports System.Data
Imports System.Configuration.ConfigurationSettings
Namespace iInterface

    Public Class Data

        Public Shared Sub AddParameter( _
            ByRef SQLCommand As SqlCommand, _
            ByVal Name As String, _
            ByVal Type As SqlDbType, _
            ByVal Direction As ParameterDirection, _
            Optional ByVal Value As String = "", _
            Optional ByVal Size As Integer = -1)

            Dim SQLParameter As New SqlParameter

            SQLParameter.ParameterName = Name
            SQLParameter.SqlDbType = Type
            SQLParameter.Direction = Direction

            If Size <> -1 Then
                SQLParameter.Size = Size
            End If
            If Value <> "" Then
                SQLParameter.Value = Value
            End If

            SQLCommand.Parameters.Add(SQLParameter)
        End Sub

        Public Shared Sub AddParameterOptional( _
            ByRef SQLCommand As SqlCommand, _
            ByVal Name As String, _
            ByVal Type As SqlDbType, _
            ByVal Direction As ParameterDirection, _
            Optional ByVal Value As String = "", _
            Optional ByVal Size As Integer = -1)

            Dim SQLParameter As New SqlParameter

            SQLParameter.ParameterName = Name
            SQLParameter.SqlDbType = Type
            SQLParameter.Direction = Direction

            If Size <> -1 Then
                SQLParameter.Size = Size
            End If


            SQLParameter.Value = Value


            SQLCommand.Parameters.Add(SQLParameter)
        End Sub


        Public Shared Sub ExecuteSQLCommand(ByVal SQLCommand As SqlCommand)
            Dim SQLConnection As New SqlConnection

            SQLConnection.ConnectionString = ConfigurationManager.ConnectionStrings("Conn").ToString
            SQLCommand.Connection = SQLConnection
            Try
                SQLConnection.Open()

                SQLCommand.ExecuteNonQuery()
            Catch ex As Exception
                Throw New Exception(ex.Message)
            Finally
                SQLConnection.Close()
            End Try


        End Sub

        Public Shared Function ExecuteSQLCommandDS(ByVal SQLCommand As SqlCommand) As DataSet
            Dim SQLConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("Conn").ToString)

            Dim SQLDataAdapter As New SqlDataAdapter
            Try

                SQLConnection.Open()
                SQLCommand.Connection = SQLConnection

                Dim SQLDataSet As DataSet = New DataSet("Temp")

                SQLDataAdapter.SelectCommand = SQLCommand

                SQLDataAdapter.Fill(SQLDataSet)

                SQLConnection.Close()

                Return SQLDataSet

            Catch ex As Exception
                Throw New Exception(ex.Message)
            Finally
                SQLConnection.Close()
            End Try
        End Function

        'Public Shared Function ExecuteSQLCommandDS(ByVal SQLCommand As SqlCommand, ByVal SQLConnection As SqlConnection) As DataSet

        '    Dim SQLDataAdapter As New SqlDataAdapter

        '    SQLCommand.Connection = SQLConnection

        '    Dim SQLDataSet As DataSet = New DataSet("Temp")

        '    SQLDataAdapter.SelectCommand = SQLCommand

        '    SQLDataAdapter.Fill(SQLDataSet)

        '    SQLConnection.Close()

        '    Return SQLDataSet

        'End Function

        Public Shared Function ExecuteSQLCommandDR(ByVal SQLCommand As SqlCommand) As SqlDataReader

            Dim SQLDataReader As SqlDataReader
            Dim SQLConnection As New SqlConnection
            Dim SQLDataAdapter As New SqlDataAdapter

            SQLConnection.ConnectionString = ConfigurationManager.ConnectionStrings("Conn").ToString
            SQLCommand.Connection = SQLConnection
            Try
                SQLConnection.Open()
                SQLDataReader = SQLCommand.ExecuteReader(CommandBehavior.CloseConnection)
                Return SQLDataReader
            Catch ex As Exception
                Throw New Exception(ex.Message)
            Finally
                SQLConnection.Close()
            End Try
        End Function


        'Public Shared Function ExecuteSQLCommandDR(ByVal SQLCommand As SqlCommand, ByVal SQLConnection As SqlConnection) As SqlDataReader

        '    Dim SQLDataReader As SqlDataReader

        '    SQLCommand.Connection = SQLConnection

        '    SQLConnection.Open()
        '    SQLDataReader = SQLCommand.ExecuteReader(CommandBehavior.CloseConnection)

        '    Return SQLDataReader

        'End Function
        Public Shared Function TestConnection(ByVal SQLCommand As SqlCommand, ByVal sConnectionString As String) As DataSet

            Dim SQLConnection As New SqlConnection(sConnectionString)
            Dim SQLDataAdapter As New SqlDataAdapter
            Try

                SQLConnection.Open()
                SQLCommand.Connection = SQLConnection
                Dim SQLDataSet As DataSet = New DataSet("Temp")
                SQLDataAdapter.SelectCommand = SQLCommand
                SQLDataAdapter.Fill(SQLDataSet)
                SQLConnection.Close()
                Return SQLDataSet
            Catch ex As Exception
                Throw New Exception(ex.Message)
            Finally
                SQLConnection.Close()
            End Try
        End Function
    End Class

End Namespace